﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DBUtility;
using IDAL;
using Model;

namespace SQLServerDAL
{
    public class Book : IBook
    {
        private readonly int BOOKSTATE_ACTIVATE = 1;
        private readonly int BOOKSTATE_DELETED = 0;
        public IList<BookInfo> SelectAllBooks()
        {
            string sqlSelect = "SELECT * FROM Books WHERE BookState = @BookState";

            IList<BookInfo> books = new List<BookInfo>();

            SqlParameter parm = new SqlParameter("@BookState", SqlDbType.Int);
            parm.Value = this.BOOKSTATE_ACTIVATE;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr,CommandType.Text,sqlSelect, parm);

            while (dr.Read())
            {
                BookInfo book = new BookInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetDateTime(8), dr.GetDateTime(9));
                books.Add(book);
            }
            dr.Close();
            return books;
        }

        public BookInfo SelectBooksByISBN(string isbn)
        {
            string sqlSelect = "SELECT * FROM Books WHERE BookState = @BookState AND ISBN = @ISBN";

            SqlParameter[] parms = {
                new SqlParameter("@BookState", SqlDbType.Int),
                new SqlParameter("@ISBN", SqlDbType.NChar, 13)};

            parms[0].Value = this.BOOKSTATE_ACTIVATE;
            parms[1].Value = isbn;


            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect, parms);

            if (dr.Read())
            {
                BookInfo book = new BookInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetDateTime(8), dr.GetDateTime(9));
                dr.Close();
                return book;
            }

            dr.Close();
            return null;
        }

        public IList<BookInfo> SelectBooksBySearch(string keyWord)
        {
            string sqlSelect = "SELECT * FROM Books WHERE BookState = @BookState AND ISBN LIKE '%'+@ISBN+'%' OR BookName LIKE '%'+@BookName+'%' OR BookAuthor LIKE '%'+@BookAuthor+'%'";
            SqlParameter[] parms = {
                new SqlParameter("@BookState", SqlDbType.Int),
                new SqlParameter("@ISBN",SqlDbType.NChar,13),
                new SqlParameter("@BookName",SqlDbType.VarChar,100),
                new SqlParameter("@BookAuthor",SqlDbType.VarChar,100)};

            parms[0].Value = this.BOOKSTATE_ACTIVATE;
            parms[1].Value = keyWord;
            parms[2].Value = keyWord;
            parms[3].Value = keyWord;

            IList<BookInfo> books = new List<BookInfo>();
            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect,parms);

            while (dr.Read())
            {
                BookInfo book = new BookInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetDateTime(8), dr.GetDateTime(9));
                books.Add(book);
            }
            dr.Close();
            return books;
        }

        public IList<BookInfo> SelectHotBooks()
        {
            string sqlSelect = "SELECT TOP 20 * FROM Books WHERE BookState = @BookState ORDER BY BookSoldNum DESC";
            IList<BookInfo> books = new List<BookInfo>();

            SqlParameter parm = new SqlParameter("@BookState", SqlDbType.Int);
            parm.Value = this.BOOKSTATE_ACTIVATE;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect, parm);
            while (dr.Read())
            {
                BookInfo book = new BookInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetDateTime(8), dr.GetDateTime(9));
                books.Add(book);
            }
            dr.Close();
            return books;
        }

        public IList<BookInfo> SelectNewBooks()
        {
            string sqlSelect = "SELECT TOP 20 * FROM Books WHERE BookState = @BookState ORDER BY BookShelfTime DESC";
            IList<BookInfo> books = new List<BookInfo>();

            SqlParameter parm = new SqlParameter("@BookState", SqlDbType.Int);
            parm.Value = this.BOOKSTATE_ACTIVATE;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect, parm);
            while (dr.Read())
            {
                BookInfo book = new BookInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetDateTime(8), dr.GetDateTime(9));
                books.Add(book);
            }
            dr.Close();
            return books;
        }

        public void InsertBook(BookInfo book)
        {
            string sqlInsert = "INSERT INTO Books(ISBN, BookName, BookAuthor, BookPicture, BookPrice, BookDesc, BookReleaseTime, BookRemainNum, BookShelfTime, BookSoldNum, BookState) VALUES (@ISBN, @BookName, @BookAuthor, @BookPicture, @BookPrice, @BookDesc, @BookReleaseTime, @BookRemainNum, @BookShelfTime, @BookSoldNum, @BookState)";

            SqlParameter[] parms = {
                new SqlParameter("@ISBN", SqlDbType.NChar, 13),
                new SqlParameter("@BookName", SqlDbType.VarChar, 100),
                new SqlParameter("@BookAuthor", SqlDbType.VarChar, 100),
                new SqlParameter("@BookPicture", SqlDbType.VarChar, 100),
                new SqlParameter("@BookPrice", SqlDbType.Float),
                new SqlParameter("@BookDesc", SqlDbType.Text),
                new SqlParameter("@BookReleaseTime", SqlDbType.DateTime),
                new SqlParameter("@BookRemainNum", SqlDbType.Int),
                new SqlParameter("@BookShelfTime", SqlDbType.DateTime),
                new SqlParameter("@BookSoldNum", SqlDbType.Int),
                new SqlParameter("@BookState", SqlDbType.Int)};

            parms[0].Value = book.ISBN;
            parms[1].Value = book.BookName;
            parms[2].Value = book.BookAuthor;
            parms[3].Value = book.BookPicture;
            parms[4].Value = book.BookPrice;
            parms[5].Value = book.BookDesc;
            parms[6].Value = book.BookReleaseTime;
            parms[7].Value = book.BookRemainNum;
            parms[8].Value = book.BookShelfTime;
            parms[9].Value = book.BookSoldNum;
            parms[10].Value = this.BOOKSTATE_ACTIVATE;

            SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopConnStr, CommandType.Text, sqlInsert, parms);
        }

        public void UpdateBook(BookInfo book)
        {
            string sqlUpdate = "UPDATE Books SET BookName = @BookName, BookAuthor = @BookAuthor, BookPicture = @BookPicture, BookPrice = @BookPrice, BookDesc = @BookDesc, BookReleaseTime = @BookReleaseTime, BookRemainNum = @BookRemainNum, BookShelfTime = @BookShelfTime, BookSoldNum = @BookSoldNum WHERE ISBN = @ISBN";

            SqlParameter[] parms = {
                new SqlParameter("@BookName", SqlDbType.VarChar, 100),
                new SqlParameter("@BookAuthor", SqlDbType.VarChar, 100),
                new SqlParameter("@BookPicture", SqlDbType.VarChar, 100),
                new SqlParameter("@BookPrice", SqlDbType.Float),
                new SqlParameter("@BookDesc", SqlDbType.Text),
                new SqlParameter("@BookReleaseTime", SqlDbType.DateTime),
                new SqlParameter("@BookRemainNum", SqlDbType.Int),
                new SqlParameter("@BookShelfTime", SqlDbType.DateTime),
                new SqlParameter("@BookSoldNum", SqlDbType.Int),
                new SqlParameter("@ISBN", SqlDbType.NChar, 13)};

            parms[0].Value = book.BookName;
            parms[1].Value = book.BookAuthor;
            parms[2].Value = book.BookPicture;
            parms[3].Value = book.BookPrice;
            parms[4].Value = book.BookDesc;
            parms[5].Value = book.BookReleaseTime;
            parms[6].Value = book.BookRemainNum;
            parms[7].Value = book.BookShelfTime;
            parms[8].Value = book.BookSoldNum;
            parms[9].Value = book.ISBN;

            SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopConnStr, CommandType.Text, sqlUpdate, parms);
        }

        public void DisableBook(string isbn)
        {
            string sqlUpdate = "UPDATE Books SET BookState = @BookState WHERE ISBN = @ISBN";
            SqlParameter[] parms = {
                new SqlParameter("@BookState", SqlDbType.Int),
                new SqlParameter("@ISBN", SqlDbType.NChar, 13)};
            
            parms[0].Value = this.BOOKSTATE_DELETED;    
            parms[1].Value = isbn;

            SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopConnStr, CommandType.Text, sqlUpdate, parms);
        }
    }
}
